package com.mobiusws.reports.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import com.mobiusws.reports.action.DailyFraudDetailAction;
import com.mobiusws.reports.action.DailyFraudSummaryAction;
import com.mobiusws.reports.action.QueryDetailAction;
import com.mobiusws.reports.model.CallType;
import com.mobiusws.reports.model.CallsNodup;
import com.mobiusws.reports.model.DailyFraudDetail;
import com.mobiusws.reports.model.DailyFraudSummary;
import com.mobiusws.reports.model.LoginInfo;
import com.mobiusws.reports.model.MenuItem;
import com.mobiusws.util.DateUtil;
import com.mobiusws.util.JsonUtil;
import com.mobiusws.util.MaskUtil;
import com.mobiusws.util.MessageUtil;
import com.mobiusws.util.OperatorConstants;
import com.mobiusws.util.SqlUtil;
import com.mobiusws.util.StringUtils;
import com.mysql.jdbc.PreparedStatement;

@Repository("queryDetailDao")
public class QueryDetailDao {
	@Resource
	private JdbcTemplate dbxJdbc;
	@Resource
	private JdbcTemplate coreJdbc;
	@Resource
	private SqlUtil sqlUtil;
	@Resource
	private CallsNodupDao callsNodupDao;
	@Resource
	private MessageUtil messageUtil;
	private Logger logger = Logger.getRootLogger();

	private int cnt;
	private List<CallsNodup> list;
	private Map<String, String> map = null;
	private Map<String, String> imeimap = null;

	private void logStmt(String sql, Object[] param) {
		logger.info(sql);
		StringBuilder logTxtBld = new StringBuilder();
		for (Object para : param) {
			logTxtBld.append(para).append(", ");
		}
		logger.info(logTxtBld.substring(0, logTxtBld.length() - 2));
	}

	private void execQuery(JdbcTemplate jdbc, String sql, Object[] param,
			BeanPropertyRowMapper rowMapper, QueryType queryType) {
		logStmt(sql, param);
		switch (queryType) {
		case INT:
			this.cnt = jdbc.queryForInt(sql, param);
			break;
		case LIST:
			this.list = jdbc.query(sql, param, rowMapper);
			break;
		}
	}

	public List<CallsNodup> getDroppedDetail(QueryDetailAction action) {
		int start = action.getStart();
		int limit = action.getLimit();
		int total = action.getTotal();

		if (start == 0 || total < 1) {
			// System.out.println(messages.getString("term.cause.normal.option1")+","+messages.getString("term.cause.normal.option2"));
			String normal1 = messageUtil.getString("term.cause.normal.option1");
			String normal2 = messageUtil.getString("term.cause.normal.option2");

			String cellId = action.getSmsisdn() == null ? "" : action
					.getSmsisdn();
			String startDate = action.getStartDate();
			String endDate = action.getEndDate();

			if (cellId.equals("") || cellId.equals(",") || cellId.equals("0")) {
				this.list = null;
				return this.list;
			}

			String sql = "";

			if (OperatorConstants.OPERATOR_NAME != null
					&& OperatorConstants.OPERATOR_NAME
							.equals("palestine_wataniya")) {
				sql = "select  s_msisdn,o_msisdn,s_imsi,s_imei,call_type,call_time,s_ci,duration,trunk_in, trunk_out,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value  "
						+ " from calls_nodup " + " WHERE call_time>= '"
						+ startDate
						+ "'  and call_time< '"
						+ endDate
						+ "' "
						+ " AND s_ci = '"
						+ cellId
						+ "' and term_cause = 2 and duration > 0 and call_type = 1  and call_id>o_call_id  "
						+ " union "
						+ "select  s_msisdn,o_msisdn,s_imsi,s_imei,call_type,call_time,s_ci,duration,trunk_in, trunk_out,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value  "
						+ " from calls_nodup "
						+ " WHERE call_time>= '"
						+ startDate
						+ "'  and call_time< '"
						+ endDate
						+ "' "
						+ " AND s_ci = '"
						+ cellId
						+ "' and term_cause = 2 and duration > 0  and call_type = 2  and substr(o_msisdn,1, 5) !="
						+ normal1
						+ " and substr(o_msisdn,1, 5) !="
						+ normal2
						+ " and call_id>o_call_id  " + " order by call_time";
			} else if (OperatorConstants.OPERATOR_NAME != null
					&& OperatorConstants.OPERATOR_NAME
							.equals("indonesia_indosat")) {
				String[] str = cellId.split("_");
				String s_lac = str[1];
				String s_ci = str[0].substring(s_lac.length());
				sql = "select  s_msisdn,o_msisdn,s_imsi,s_imei,call_type,call_time,s_ci,duration,trunk_in, trunk_out,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value  "
						+ " from calls_nodup "
						+ " WHERE call_time>= '"
						+ startDate
						+ "'  and call_time< '"
						+ endDate
						+ "' "
						+ " AND s_ci = '"
						+ s_ci
						+ "' and s_lac = '"
						+ s_lac
						+ "' and term_cause != "
						+ normal1
						+ " and term_cause != "
						+ normal2
						+ "  and ( call_type >= 1 and call_type <=2 ) and call_id>o_call_id "
						+ " order by call_time";
			} else {
				sql = "select  s_msisdn,o_msisdn,s_imsi,s_imei,call_type,call_time,s_ci,duration,trunk_in, trunk_out,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value  "
						+ " from calls_nodup "
						+ " WHERE call_time>= '"
						+ startDate
						+ "'  and call_time< '"
						+ endDate
						+ "' "
						+ " AND s_ci = '"
						+ cellId
						+ "' and term_cause != "
						+ normal1
						+ " and term_cause != "
						+ normal2
						+ "  and ( call_type >= 1 and call_type <=2 ) and call_id>o_call_id  "
						+ " order by call_time";
			}

			this.list = coreJdbc.query(sql, new Object[] {}, new RowMapper() {
				@Override
				public Object mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					CallsNodup calls = new CallsNodup();
					calls.setSmsisdn(MaskUtil.intXORMask(rs.getLong("s_msisdn")));
					calls.setOmsisdn(MaskUtil.intXORMask(rs.getLong("o_msisdn")));
					calls.setSimsi(rs.getLong("s_imsi"));
					calls.setSimei(rs.getLong("s_imei"));
					calls.setHandsetBrand(getVendorModel(rs.getString("s_imei"))[0]);
					calls.setHandsetModel(getVendorModel(rs.getString("s_imei"))[1]);
					calls.setCallType(rs.getInt("call_type"));
					calls.setCallTypeValue(rs.getString("call_type_value"));
					calls.setCallTime(setCallTime(rs.getTimestamp("call_time")));
					calls.setSci(rs.getInt("s_ci"));
					calls.setDuration(rs.getInt("duration"));
					calls.setTrunkInName(getTrunk(rs.getInt("trunk_in")));
					calls.setTrunkOutName(getTrunk(rs.getInt("trunk_out")));
					return calls;
				}
			});
			action.setTotal(this.list.size());
		}

		List<CallsNodup> returnList = null;
		if (action.getTotal() > 0) {
			int startIndex = start * limit;
			int endIndex = start * limit + limit;
			int size = this.list.size();
			if (endIndex > size) {
				endIndex = size;
			}
			returnList = this.list.subList(startIndex, endIndex);
		} else {
			returnList = new ArrayList<>();
		}

		// for (CallsNodup item : returnList) {
		// System.out.println(item.getSmsisdn() + ", " + item.getOmsisdn()
		// + ", " + item.getCallTime());
		// }
		return returnList;
	}

	public List<CallsNodup> getQuerySubDetail(QueryDetailAction action) {
		int start = action.getStart();
		int limit = action.getLimit();
		int total = action.getTotal();

		if (start == 0 || total < 1) {
			// List<CallsNodup> tmp_list = null;

			// int queryType = filter.getQueryType();
			String s_msisdn = action.getSmsisdn();
			String startDate = action.getStartDate();
			String endDate = action.getEndDate();

			// insert audit log -- 2011-10-11
			// String data = "msisdn or imsi=" + s_msisdn + ",startDate=" +
			// startDate + ",endDate=" + endDate;
			// AuditLogUtil.writeAuditLog("Query", session, "Query Detail",
			// data);

			s_msisdn = StringUtils.matchString(s_msisdn);

			if (s_msisdn.equals("") || s_msisdn.equals(",")
					|| s_msisdn.equals("0")) {
				return null;
			}

			String sql = " SELECT s_msisdn,o_msisdn,s_imsi,s_imei,call_type,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value,call_time,s_ci,duration,trunk_in,trunk_out FROM calls_nodup "
					+ " WHERE call_time>= '"
					+ startDate
					+ "'  and call_time< '"
					+ endDate
					+ "' "
					+ " AND s_msisdn in ("
					+ s_msisdn
					+ ") and ( call_type = 1 or call_type =2 ) and call_id>o_call_id "
					+ " union "
					+ " SELECT  s_msisdn,o_msisdn,s_imsi,s_imei,call_type,case when call_type=1 then 'MOC' when call_type=2 then 'MTC' end call_type_value,call_time,s_ci,duration,trunk_in,trunk_out FROM calls_nodup "
					+ " WHERE call_time>= '"
					+ startDate
					+ "'  and call_time< '"
					+ endDate
					+ "' "
					+ " AND o_msisdn in ("
					+ s_msisdn
					+ ") and ( call_type = 1 or call_type =2 )  and call_id>o_call_id "
					+ " order by call_time";
			this.list = coreJdbc.query(sql, new Object[] {}, new RowMapper() {
				@Override
				public Object mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					CallsNodup calls = new CallsNodup();
					calls.setSmsisdn(MaskUtil.intXORMask(rs.getLong("s_msisdn")));
					calls.setOmsisdn(MaskUtil.intXORMask(rs.getLong("o_msisdn")));
					calls.setSimsi(rs.getLong("s_imsi"));
					calls.setSimei(rs.getLong("s_imei"));
					calls.setHandsetBrand(getVendorModel(rs.getString("s_imei"))[0]);
					calls.setHandsetModel(getVendorModel(rs.getString("s_imei"))[1]);
					calls.setCallType(rs.getInt("call_type"));
					calls.setCallTypeValue(rs.getString("call_type_value"));
					calls.setCallTime(setCallTime(rs.getTimestamp("call_time")));
					calls.setSci(rs.getInt("s_ci"));
					calls.setDuration(rs.getInt("duration"));
					calls.setTrunkInName(getTrunk(rs.getInt("trunk_in")));
					calls.setTrunkOutName(getTrunk(rs.getInt("trunk_out")));
					return calls;
				}
			});
			action.setTotal(this.list.size());
		}

		// if(action.getStart()==0 || action.getTotal()<1) {
		// String sqlCnt = sqlUtil.getSqlById("getFraudDetailByMsisdnCount");
		// Object[] param = new Object[] { action.getSelDate(),
		// action.getMsisdn() };
		// execQuery(dbxJdbc, sqlCnt, param, null, QueryType.INT);
		// action.setTotal(cnt);
		// }
		// if (action.getTotal() > 0) {
		// String sql = sqlUtil.getSqlById("getFraudDetailByMsisdn");
		// Object[] params = new Object[] { action.getSelDate(),
		// action.getMsisdn(), action.getStart(), action.getLimit() };
		// BeanPropertyRowMapper rowMapper = new BeanPropertyRowMapper(
		// DailyFraudDetail.class);
		// execQuery(dbxJdbc, sql, params, rowMapper, QueryType.LIST);
		// } else {
		// this.list = new ArrayList();
		// }
		List<CallsNodup> returnList = null;
		if (action.getTotal() > 0) {
			int startIndex = start * limit;
			int endIndex = start * limit + limit;
			int size = this.list.size();
			if (endIndex > size) {
				endIndex = size;
			}
			returnList = this.list.subList(startIndex, endIndex);
		} else {
			returnList = new ArrayList<>();
		}

		// for (CallsNodup item : returnList) {
		// System.out.println(item.getSmsisdn() + ", " + item.getOmsisdn()
		// + ", " + item.getCallTime());
		// }
		return returnList;
	}

	public Map<String, String> getVendorModelList() {
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select tac,make_name_short,model_name_short from make_model";
		imeimap = new HashMap<String, String>();
		RowMapper rowMapper = new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				imeimap.put(
						rs.getString("tac"),
						rs.getString("make_name_short") + "mbws"
								+ rs.getString("model_name_short"));
				return null;
			}
		};
		coreJdbc.query(sql, new Object[] {}, rowMapper);

		return imeimap;
	}

	public String[] getVendorModel(String imei) {
		String[] str = new String[2];
		try {
			str[0] = "NA";
			str[1] = "NA";
			if (imei != null && imei.length() > 8) {
				imei = imei.substring(0, getTacLength(imei));
				if (imeimap == null) {
					logger.debug("init vendor/model list");
					getVendorModelList();
				}
				Iterator it = imeimap.entrySet().iterator();
				while (it.hasNext()) {
					Map.Entry stat = (Map.Entry) it.next();
					if (stat.getKey().equals(String.valueOf(imei))) {
						str[0] = stat.getValue().toString().split("mbws")[0];
						str[1] = stat.getValue().toString().split("mbws")[1];
						break;
					}
				}
			}
		} catch (Exception e) {
			logger.error("get vendor/model error");
		}
		return str;
	}

	public int getTacLength(String imei) {
		int prefixLength = 14 - imei.length();
		int tacLength = 8 - prefixLength;
		return tacLength;
	}

	public List<List<Object>> getQuerySubDetailExport(int queryType) {
		List<List<Object>> ls = new ArrayList<List<Object>>();
		List<Object> inner_ls = null;
		CallsNodup call = null;
		if (queryType == 3) {
			for (int i = 0; i < list.size(); i++) {
				call = list.get(i);
				inner_ls = new ArrayList<Object>();
				inner_ls.add(call.getSmsisdn());
				inner_ls.add(call.getOmsisdn());
				inner_ls.add(call.getSimsi());
				inner_ls.add(call.getSimei());
				inner_ls.add(call.getHandsetBrand());
				inner_ls.add(call.getHandsetModel());
				inner_ls.add(call.getCallType());
				inner_ls.add(call.getCallTypeValue());
				inner_ls.add(DateUtil.date2String("yyyy-MM-dd HH:mm:ss",
						call.getCallTime()));
				inner_ls.add(call.getSci());
				inner_ls.add(call.getDuration());
				inner_ls.add(call.getTrunkInName());
				inner_ls.add(call.getTrunkOutName());

				ls.add(inner_ls);
			}
		} else if (queryType == 4) {
			for (int i = 0; i < list.size(); i++) {
				call = list.get(i);
				inner_ls = new ArrayList<Object>();
				inner_ls.add(call.getSmsisdn());
				inner_ls.add(call.getOmsisdn());
				inner_ls.add(call.getSimsi());
				inner_ls.add(call.getSimei());
				inner_ls.add(call.getHandsetBrand());
				inner_ls.add(call.getHandsetModel());
				inner_ls.add(call.getCallType());
				inner_ls.add(call.getCallTypeValue());
				inner_ls.add(DateUtil.date2String("yyyy-MM-dd HH:mm:ss",
						call.getCallTime()));
				inner_ls.add(call.getSci());
				inner_ls.add(call.getDuration());
				inner_ls.add(call.getTrunkInName());
				inner_ls.add(call.getTrunkOutName());

				ls.add(inner_ls);
			}
		} else {
			for (int i = 0; i < list.size(); i++) {
				call = list.get(i);
				inner_ls = new ArrayList<Object>();
				inner_ls.add(call.getSmsisdn());
				inner_ls.add(call.getOmsisdn());
				inner_ls.add(call.getSimsi());
				inner_ls.add(call.getSimei());
				inner_ls.add(call.getHandsetBrand());
				inner_ls.add(call.getHandsetModel());
				inner_ls.add(call.getCallType());
				inner_ls.add(call.getCallTypeValue());
				inner_ls.add(DateUtil.date2String("yyyy-MM-dd HH:mm:ss",
						call.getCallTime()));
				inner_ls.add(call.getSci());
				inner_ls.add(call.getDuration());
				inner_ls.add(call.getTrunkInName());
				inner_ls.add(call.getTrunkOutName());

				ls.add(inner_ls);
			}
		}
		return ls;
	}

	private Date setCallTime(Date call_time) {
		Date date = null;
		try {
			date = DateUtil.string2Date("yyyy-MM-dd HH:mm:ss", DateUtil
					.GMTCovert("GMT+0:00", OperatorConstants.TIMEZONE, DateUtil
							.date2String("yyyy-MM-dd HH:mm:ss", call_time)));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return date;
	}

	private Date setGMTCallTime(Date call_time) throws Exception {
		return DateUtil.string2Date("yyyy-MM-dd HH:mm:ss", DateUtil.GMTCovert(
				OperatorConstants.TIMEZONE, "GMT+0:00",
				DateUtil.date2String("yyyy-MM-dd HH:mm:ss", call_time)));

	}

	public String getTrunk(int trunk_id) {
		String trunk_name = "";
		try {
			if (map == null) {
				logger.debug("init trunk list");
				getTrunkList();
			}
			Iterator it = map.entrySet().iterator();
			while (it.hasNext()) {
				Map.Entry stat = (Map.Entry) it.next();
				if (stat.getKey().equals(String.valueOf(trunk_id))) {
					trunk_name = stat.getValue().toString();
					break;
				}
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return trunk_name;
	}

	public Map<String, String> getTrunkList() {
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select trunk_id,trunk_value from trunk";
		map = new HashMap<String, String>();
		RowMapper rowMapper = new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				map.put(rs.getString("trunk_id"), rs.getString("trunk_value"));
				return null;
			}
		};
		coreJdbc.query(sql, new Object[] {}, rowMapper);
		return map;
	}

	public String getSmsisdnByImsi(QueryDetailAction action, String imsi) {
		String s_msisdn = "";

		String startDate = action.getStartDate();
		String endDate = action.getEndDate();
		s_msisdn = String.valueOf(callsNodupDao.getMsisdnByImsi(imsi,
				startDate, endDate));
		if (s_msisdn.equals("0"))
			s_msisdn = "";

		return s_msisdn;
	}
}
